package dtt;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class UserBean implements java.io.Serializable {
 int userId=0;
 String userName= "";
 String userPassword="";
 String userRole="";
 String userEmail ="";
 String userTel ="";
 String userAddress ="";
 String userInfo ="";
 
 public String getUserName(){
	 return userName;
 }
 public void setUserName(String userName){
	 this.userName = userName;
 }
public int getUserId() {
	return userId;
}
public void setUserId(int userId) {
	this.userId = userId;
}
public String getUserPassword() {
	return userPassword;
}
public void setUserPassword(String userPassword) {
	this.userPassword = userPassword;
}
public String getUserRole() {
	return userRole;
}
public void setUserRole(String userRole) {
	this.userRole = userRole;
}
public String getUserEmail(){
	return userEmail;
}
public String getUserTel(){
	return userTel;
}
public String getUserAddress(){
	return userAddress;
}
public String getUserInfo(){
	return userInfo;
}

public int generateUserId(){
	
	int count = 0;
	Connection con1 = DBConnection.getConnection();
	String template = "select count(*) from t_user";
	try{
		PreparedStatement ps1 = con1.prepareStatement(template);
		
		ResultSet rs1 = ps1.executeQuery();
		rs1.next();
		count = rs1.getInt(1);
		} //end try
	catch(SQLException e){
	 e.printStackTrace();
	}// end catch
	count +=1;

	return count+1;
}
public boolean addTeamMember(String username, String userpassword,String useremail, String usertel, String useraddress, String userinfo)
{
//	DefectBean outputbean = null;
	boolean flag = false;
	Connection con = DBConnection.getConnection();
	String template ="insert into t_user (userid,username,userpassword,useremail,usertel,useraddress,userinfo )values" +
			                         "(?,?,?,?,?,?,?)";
	try {
	PreparedStatement ps = con.prepareStatement(template);
	int userid= generateUserId();
	//String defectId = generateDefectId(projectId);
			
	ps.setInt(1,userid);
	ps.setString(2, username);
	ps.setString(3, userpassword);
	ps.setString(4, useremail);
	ps.setString(5, usertel);
	ps.setString(6,useraddress);
	ps.setString(7,userinfo);
			
	int count = ps.executeUpdate();
	ps.close();
	
	
	DBConnection.releaseConnection(con);
	con = null;
	
	if(count>0){
		flag = true;
	}
	else{
		flag = false;
	}
	}
	catch(SQLException e){
	 System.out.println("WRONG HERE");
	 e.printStackTrace();
	}				
	return flag;
	}
public ArrayList getUsers() {
	// ProjectBean project = new ProjectBean();
	ArrayList userbeanlist = new ArrayList();
	String template = "select username from t_user";
	try {
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = con.prepareStatement(template);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
				userbeanlist.add(rs.getString(1));
		}// end while
		ps.close();
		con = null;
	}// end try
	catch (SQLException e) {
		e.printStackTrace();
	}
	return userbeanlist;
}

public ArrayList getRoles() {
	// ProjectBean project = new ProjectBean();
	ArrayList userrolelist = new ArrayList();
	String template = "select ROLEDESC from t_role group by ROLEDESC";
	try {
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = con.prepareStatement(template);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
				userrolelist.add(rs.getString(1));
		}// end while
		ps.close();
		con = null;
	}// end try
	catch (SQLException e) {
		e.printStackTrace();
	}
	return userrolelist;
 }
public boolean addRole(String projectname, String username, String rolename, String newrole){
	System.out.println("project name:" + projectname);
	System.out.println("user name:" +username);
	System.out.println("role name:" + rolename);
	System.out.println("new role name:" + newrole);
//	DefectBean outputbean = null;
	int projectid=0;
	int userid = 0;
	boolean flag = false;
	Connection con = DBConnection.getConnection();
	String template ="insert into t_role (userid,ROLEDESC,PROJECTID)values" +
			                         "(?,?,?)";
	try {
		// get projectid by project name
		PreparedStatement ps = con.prepareStatement("select projectId from t_project where projectNbr = ?");
		ps.setString(1,projectname);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			 projectid = rs.getInt(1);
		}
		// get userid by username
		ps = con.prepareStatement("select userid from t_user where username = ?");
		ps.setString(1,username);
		rs = ps.executeQuery();
		while (rs.next()) {
			 userid = rs.getInt(1);
		}
		
		
	ps = con.prepareStatement(template);

	//String defectId = generateDefectId(projectId);
			
	ps.setInt(1,userid);
	ps.setString(2, newrole);
	ps.setInt(3, projectid);

			
	int count = ps.executeUpdate();
	ps.close();
	
	
	DBConnection.releaseConnection(con);
	con = null;
	
	if(count>0){
		flag = true;
	}
	else{
		flag = false;
	}
	}
	catch(SQLException e){
	 System.out.println("WRONG HERE");
	 e.printStackTrace();
	}				
	return flag;
}

//getDetailRoles
public ArrayList getDetailRoles() {
	// ProjectBean project = new ProjectBean();
	ArrayList userrolelist = new ArrayList();
	String template = "select t_project.projectNbr, t_user.username, ROLEDESC from t_project, t_user,t_role where t_project.projectId = t_role.projectId and t_user.userId = t_role.userId";
	try {
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = con.prepareStatement(template);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
				userrolelist.add(rs.getString(1));
				userrolelist.add(rs.getString(2));
				userrolelist.add(rs.getString(3));
		}// end while
		ps.close();
		con = null;
	}// end try
	catch (SQLException e) {
		e.printStackTrace();
	}
	return userrolelist;
 }
public boolean delRole(String detailrole) {
	String projectname;
	String username;
	String newrole;
	String rolename="";
	projectname = detailrole.split("===")[0];
	username = detailrole.split("===")[1];
	newrole = detailrole.split("===")[2];
	
	System.out.println("project name:" + projectname);
	System.out.println("user name:" +username);
	System.out.println("role name:" + rolename);
	System.out.println("new role name:" + newrole);
//	DefectBean outputbean = null;
	int projectid=0;
	int userid = 0;
	boolean flag = false;
	Connection con = DBConnection.getConnection();
	String template ="delete from t_role where userid = ? and ROLEDESC = ? and PROJECTID = ? ";
	try {
		// get projectid by project name
		PreparedStatement ps = con.prepareStatement("select projectId from t_project where projectNbr = ?");
		ps.setString(1,projectname);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			 projectid = rs.getInt(1);
		}
		// get userid by username
		ps = con.prepareStatement("select userid from t_user where username = ?");
		ps.setString(1,username);
		rs = ps.executeQuery();
		while (rs.next()) {
			 userid = rs.getInt(1);
		}
		
		
	ps = con.prepareStatement(template);

	//String defectId = generateDefectId(projectId);
			
	ps.setInt(1,userid);
	ps.setString(2, newrole);
	ps.setInt(3, projectid);

			
	int count = ps.executeUpdate();
	ps.close();
	
	
	DBConnection.releaseConnection(con);
	con = null;
	
	if(count>0){
		flag = true;
	}
	else{
		flag = false;
	}
	}
	catch(SQLException e){
	 System.out.println("WRONG HERE");
	 e.printStackTrace();
	}				
	return flag;
 }
public ArrayList getUserDetail() {
	// ProjectBean project = new ProjectBean();
	ArrayList userrolelist = new ArrayList();
	String template = "select * from t_user where username = ?";
	try {
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = con.prepareStatement(template);
		ps.setString(1, this.userName);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			// username,userpassword,useremail,usertel,useraddress,userinfo
				//userrolelist.add(rs.getString("username"));
				this.userPassword=(rs.getString("userpassword"));
				this.userEmail =(rs.getString("useremail")+" ");
				this.userTel =(rs.getString("usertel")+" ");
				this.userAddress=(rs.getString("useraddress"));
				this.userInfo =(rs.getString("userinfo")+" ");
		}// end while
		ps.close();
		con = null;
	}// end try
	catch (SQLException e) {
		e.printStackTrace();
	}	
	System.out.println("profile:");
	System.out.println(this.userEmail);
	System.out.println(this.userTel);
	System.out.println(this.userAddress);
	System.out.println(this.userInfo);
	
	return userrolelist;
 }

public boolean updateTeamMember(String username, String userpassword,String useremail, String usertel, String useraddress, String userinfo)
{
//	DefectBean outputbean = null;
	boolean flag = false;
	Connection con = DBConnection.getConnection();
	String template ="update t_user set userpassword=?,useremail=?,usertel=?,useraddress=?,userinfo=? where username=?";
	try {
	PreparedStatement ps = con.prepareStatement(template);
			
	ps.setString(6, username);
	ps.setString(1, userpassword);
	ps.setString(2, useremail);
	ps.setString(3, usertel);
	ps.setString(4,useraddress);
	ps.setString(5,userinfo);
			
	int count = ps.executeUpdate();
	ps.close();
	
	
	DBConnection.releaseConnection(con);
	con = null;
	
	if(count>0){
		flag = true;
	}
	else{
		flag = false;
	}
	}
	catch(SQLException e){
	 System.out.println("WRONG HERE");
	 e.printStackTrace();
	}				
	return flag;
	}

}
